package com.evry.repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.evry.domain.collateralitem.CollateralItem;
import com.evry.domain.collateralitem.Mortgage;
import com.evry.domain.collateralitem.Mortgagor;
import com.evry.support.Fractions;

@Repository("collateralItemDao")
public class CollateralItemDaoImpl extends AbstractDaoSupport implements
		CollateralItemDao {
	
	@Autowired
	private Fractions fractions;

	private static final String KOPI_NR = "KOPI_NR";

	private static final String UNDER_TYPE = "UNDER_TYPE";

	private static final String GET_ALL_COLLATERALITEMS = "SELECT OBHO.*, obbe.beskrivelse "
			+ "FROM ksd_obho AS obho "
			+ "LEFT OUTER JOIN ksd_obbe AS obbe ON obho.objeckt_nr=obbe.fk_ksd_obhoobjekt AND obho.kopi_nr=obbe.fk_ksd_obhokopi_nr "
			+ "WHERE obho.kopi_nr = :KOPI_NR";

	private static final String GET_MORTGAGES_BY_COLLATERALITEM_NR = "SELECT * "
			+ "FROM ksd_obhe "
			+ "WHERE FK_KSD_OBHOOBJEKT = :OBJEKT_NR AND FK_KSD_OBHOKOPI_NR = :KOPI_NR";

	private static final String GET_MORTGAGORS_BY_COLLATERALITEM_NR = "SELECT * "
			+ "FROM ksd_obei AS obei "
			+ "LEFT OUTER JOIN ksd_obfe AS obfe ON obei.fk_ksd_obhoobjekt=obfe.fk_ksd_obhoobjekt AND obei.fk_ksd_obhokopi_nr=obfe.fk_ksd_obhokopi_nr "
			+ "WHERE fk_ksd_obhoobjekt = :OBJEKT_NR AND fk_ksd_obhokopi_nr = :KOPI_NR";

	@Autowired
	public CollateralItemDaoImpl(DataSource dataSource) {
		super(dataSource);
	}

	@Override
	public List<CollateralItem> getAllCollateralItems() {
		Map<String, Object> namedParameters = new HashMap<String, Object>();
		namedParameters.put(KOPI_NR, 0);
		List<CollateralItem> collateralItems = getNamedParameterJdbcTemplate()
				.query(GET_ALL_COLLATERALITEMS, namedParameters,
						new RowMapper<CollateralItem>() {
							public CollateralItem mapRow(ResultSet rs,
									int rowNum) throws SQLException {
								CollateralItem collateralItem = new CollateralItem();
								collateralItem.setDesignation(rs
										.getString(UNDER_TYPE));
								collateralItem.setStatus(rs
										.getString("FK_KSD_OBSTSTATUS"));
								collateralItem.setType(rs
										.getString("FK_KSD_OBTYOBJEKT"));
								collateralItem.setUniquekey(0); // no mapping
																// information
								collateralItem.setDescription(rs
										.getString("beskrivelse"));
								collateralItem.setObjectId(rs
										.getInt("OBJECKT_NR"));
								return collateralItem;
							}
						});

		return collateralItems;
	}

	@Override
	public void setMortgages(CollateralItem collateralItem) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();
		namedParameters.put("OBJEKT_NR", collateralItem.getObjectNumber());
		namedParameters.put(KOPI_NR, 0);
		List<Mortgage> mortgages = getNamedParameterJdbcTemplate().query(
				GET_MORTGAGES_BY_COLLATERALITEM_NR, 
				namedParameters,
				new RowMapper<Mortgage>() {
					public Mortgage mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Mortgage mortgage = new Mortgage();
						mortgage.setCollateralUniquekey(rs
								.getLong("FK_KSD_SIKKSIKKERH"));
						mortgage.setPriority(rs.getInt("PRIORITET"));
						return mortgage;
					}
				});
		collateralItem.getMortgages().addAll(mortgages);

	}

	@Override
	public void setMortgagors(CollateralItem collateralItem) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();
		namedParameters.put("OBJEKT_NR", collateralItem.getObjectNumber());
		namedParameters.put(KOPI_NR, 0);
		List<Mortgagor> mortgagors = getNamedParameterJdbcTemplate().query(
				GET_MORTGAGORS_BY_COLLATERALITEM_NR, 
				namedParameters, 
				new RowMapper<Mortgagor>(){
					public Mortgagor mapRow(ResultSet rs, int rowNum) throws SQLException {
						Mortgagor mortgagor = new Mortgagor();
						mortgagor.setCustomerId(rs.getLong("FK_KCI_KUNDKUNDEID"));
						//mortgagor.setInhabitCode(rs.getString("BEBO_LEIE")); BEBO_LEIE should be used according to mapping, but does not exist!?
						mortgagor.setOwnersShareDenominator(fractions.calculateDenominator(Double.valueOf(rs.getString("EIER_ANDEL"))));
						mortgagor.setOwnersShareNumerator(fractions.calculateNumerator(Double.valueOf(rs.getString("EIER_ANDEL"))));	
						mortgagor.setSpousePartnerApproval(rs.getInt("EKTEFELLE_SAMTYKKE"));
						return mortgagor;
					}
				});
		collateralItem.getMortgagors().addAll(mortgagors);	
	}
}
